﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using BioModel;
using System.Runtime.Serialization;

namespace BioDAL
{
    [DataContract]
    public class KundeDAL : IKundeDAL
    {
        #region Connection
        private SqlConnection sqlCn = null;

        public void OpenConnection(string connectionString)
        {
            Console.WriteLine("Connection Established");
            sqlCn = new SqlConnection();
            sqlCn.ConnectionString = connectionString;
            sqlCn.Open();
        }

        public void CloseConnection()
        {
            sqlCn.Close();
        }
        #endregion

        #region Kunde
        public int InsertKunde(int telefonNr, string navn, string email, string password)
        {
            try
            {
                string sql = string.Format("Insert into bioKunde" +
                     "(telefonNr,navn,email, password) Values" +
                     "('{0}', '{1}', '{2}', '{3}')", telefonNr, navn, email, password);

                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException se)
            {
                Console.Write("!SQL Insert Kunde fejl " + se);

            }
            return telefonNr;
        }

        public void EditKunde(int telefonNr, string navn, string email)
        {
            string sql = string.Format("Update bioKunde Set navn = '{0}', email = '{1}' Where telefonNr = {2}"
                , navn, email, telefonNr);
            Console.WriteLine(sql);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        private static Kunde BuildKunde(SqlDataReader dr)
        {
            return new Kunde(
                                    (int)(dr["telefonNr"]),
                                    (string)(dr["navn"]),
                                    (string)(dr["email"]),
                                    (string)(dr["password"])
                                    );
        }

        public List<Kunde> AlleKunder()
        {
            List<Kunde> kundeList = new List<Kunde>();
            string sql = string.Format("Select * From bioKunde");
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    kundeList.Add(BuildKunde(dr));

                }
                dr.Close();
            }
            return kundeList;
        }

        public Kunde GetEnKunde(int tlf)
        {
            Kunde kunde = new Kunde();
            string sql = string.Format("Select * from bioKunde where telefonNr = {0}", tlf);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    kunde = BuildKunde(dr);
                }
                dr.Close();
            }
            return kunde;
        }
        #endregion
    }
}
